#!/usr/bin/python
# -*- coding: utf-8 -*-

DB_HOST, DB_NAME = 'localhost', 'oculus'
DB_USER, DB_PASSWD = 'dvallejo', 'sensor'

import sys
import MySQLdb

import ObjectInfo, ObjectInfoEvent, Position
from ObjectInfo import *
from ObjectInfoEvent import *
from Position import *

class DB_Util:

    def __init__ (self, host = DB_HOST, db = DB_NAME, user = DB_USER, passwd = DB_PASSWD):

        try:
            self._conn = MySQLdb.connect (host, user, passwd, db)
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            sys.exit(1)

    def get_objects_info (self, camera, frame):

        cursor = self._conn.cursor()
        objects = {}

        # Get the object id, position and displacements.
        cursor.execute("""SELECT id_obj, x, y, hs, vs FROM Tracking WHERE camera=%s and frame=%s""", (camera, frame))
        for row in cursor.fetchall():
            objects[row[0]] = ObjectInfo(row[0], Position(int(row[1]), int(row[2])), int(row[3]), int(row[4]))

        # Get the object roles.
        for k in objects.keys():
            cursor.execute("""SELECT class, membership_value FROM Tracking_Classification WHERE camera=%s and frame=%s and obj_id=%s""", (camera, frame, k))
            for row in cursor.fetchall():
                objects[k].add_role(row[0], float(row[1]))

        # Get the object locations.
        for k in objects.keys():
            cursor.execute("""SELECT area, membership_value FROM Tracking_Zones WHERE camera=%s and frame=%s and obj_id=%s""", (camera, frame, k))
            for row in cursor.fetchall():
                objects[k].add_location(row[0], float(row[1]))

        return ObjectInfoEvent(camera, frame, objects.values())

    def get_object_speed (self, obj, camera, frame):

        cursor = self._conn.cursor()

        cursor.execute("""SELECT MOV FROM Speed_Object_Displacements WHERE camera=%s and frame=%s and id_obj=%s""", (camera, frame, obj))
        try:
            return float(cursor.fetchone()[0])
        except TypeError:
            return 0.0

    def get_object_displacement (self, obj, camera, frame, precision, axis):

        cursor = self._conn.cursor()

        cursor.execute("""SELECT """ + axis + """ FROM Tracking WHERE camera=%s and frame=%s and id_obj=%s""", (camera, frame, obj))
        try:
            v2 = int(cursor.fetchone()[0])
        except TypeError:
            return 0

        cursor.execute("""SELECT """ + axis + """ FROM Tracking WHERE camera=%s and frame=%s and id_obj=%s""", (camera, frame - precision, obj))
        try:
            v1 = int(cursor.fetchone()[0])
        except TypeError:
            return 0

        return v2 - v1

    def __del__ (self):

        self._conn.commit()
        self._conn.close()
